using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using hao_Common.Dapper.Db;

namespace hao_Common.Dapper.Repository
{
    /// <summary>
    /// Dapper通用仓储实现，支持MySQL、异步CRUD和软删除
    /// </summary>
    public class DapperGenericRepository<T> : IGenericRepository<T> where T : class, ISoftDelete
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly string _tableName;

        /// <summary>
        /// 受保护的连接工厂属性，供子类访问
        /// </summary>
        protected IDbConnectionFactory ConnectionFactory => _connectionFactory;

        public DapperGenericRepository(IDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
            _tableName = typeof(T).Name;
        }

        public async Task<T> GetByIdAsync(object id)
        {
            using var conn = _connectionFactory.CreateConnection();
            var sql = $"SELECT * FROM `{_tableName}` WHERE Id = @Id AND IsDeleted = 0 LIMIT 1";
            return await conn.QueryFirstOrDefaultAsync<T>(sql, new { Id = id });
        }

        public async Task<IEnumerable<T>> GetAllAsync()
        {
            using var conn = _connectionFactory.CreateConnection();
            var sql = $"SELECT * FROM `{_tableName}` WHERE IsDeleted = 0";
            return await conn.QueryAsync<T>(sql);
        }

        public async Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate)
        {
            // Dapper不支持表达式树转SQL，这里建议用GetAllAsync后内存过滤，或扩展支持
            var all = await GetAllAsync();
            return all.AsQueryable().Where(predicate.Compile());
        }

        public async Task AddAsync(T entity)
        {
            using var conn = _connectionFactory.CreateConnection();
            var props = typeof(T).GetProperties();
            var columns = string.Join(",", props.Select(p => $"`{p.Name}`"));
            var values = string.Join(",", props.Select(p => $"@{p.Name}"));
            var sql = $"INSERT INTO `{_tableName}` ({columns}) VALUES ({values})";
        var a =    await conn.ExecuteAsync(sql, entity);
        }

        public async Task UpdateAsync(T entity)
        {
            using var conn = _connectionFactory.CreateConnection();
            var props = typeof(T).GetProperties().Where(p => p.Name != "Id");
            var setClause = string.Join(",", props.Select(p => $"`{p.Name}` = @{p.Name}"));
            var sql = $"UPDATE `{_tableName}` SET {setClause} WHERE Id = @Id";
            await conn.ExecuteAsync(sql, entity);
        }

        public async Task DeleteAsync(object id)
        {
            using var conn = _connectionFactory.CreateConnection();
            var sql = $"UPDATE `{_tableName}` SET IsDeleted = 1 WHERE Id = @Id";
            await conn.ExecuteAsync(sql, new { Id = id });
        }

        public async Task RemoveAsync(object id)
        {
            using var conn = _connectionFactory.CreateConnection();
            var sql = $"DELETE FROM `{_tableName}` WHERE Id = @Id";
            await conn.ExecuteAsync(sql, new { Id = id });
        }
        
        /// <summary>
        /// 分页查询
        /// </summary>
        public async Task<(IEnumerable<T> Items, int TotalCount)> GetPagedAsync(int pageNumber, int pageSize, string orderBy = "Id", bool isDescending = false)
        {
            using var conn = _connectionFactory.CreateConnection();
            
            // 计算偏移量
            var offset = (pageNumber - 1) * pageSize;
            
            // 构建排序子句
            var orderDirection = isDescending ? "DESC" : "ASC";
            var orderClause = $"ORDER BY `{orderBy}` {orderDirection}";
            
            // 查询总数
            var countSql = $"SELECT COUNT(*) FROM `{_tableName}` WHERE IsDeleted = 0";
            var totalCount = await conn.QuerySingleAsync<int>(countSql);
            
            // 分页查询数据
            var dataSql = $"SELECT * FROM `{_tableName}` WHERE IsDeleted = 0 {orderClause} LIMIT @Limit OFFSET @Offset";
            var items = await conn.QueryAsync<T>(dataSql, new { Limit = pageSize, Offset = offset });
            
            return (items, totalCount);
        }
    }
}